﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    public partial class MainService : ICInterview
    {
        public CInterviewInfo CInterview_GetInfo(int CandidateID, int RecruitmentID, int Ordinal)
        {
            CInterviewInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_CandidateInterview_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CInterview_CandidateID", CandidateID);
            dbCmd.Parameters.AddWithValue("@CInterview_RecruitmentID", RecruitmentID);
            dbCmd.Parameters.AddWithValue("@CInterview_Ordinal", Ordinal);
            SqlDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new CInterviewInfo();
                    retVal.ID = Convert.ToInt32(dr["CInterview_ID"]);
                    retVal.CandidateID = Convert.ToInt32(dr["CInterview_CandidateID"]);
                    retVal.Ordinal = Convert.ToInt32(dr["CInterview_Ordinal"]);
                    retVal.Date = dr["CInterview_Date"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["CInterview_Date"]);
                    retVal.Interviewer = Convert.ToString(dr["CInterview_Interviewer"]);
                    retVal.InterviewerPosition =dr["CInterview_InterviewerPosition"]==DBNull.Value?0: Convert.ToInt32(dr["CInterview_InterviewerPosition"]);
                    retVal.OverallComments = Convert.ToString(dr["CInterview_OverallComments"]);
                    retVal.PositionOffer = dr["CInterview_PositionOffer"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CInterview_PositionOffer"]);
                    retVal.DepartmentOffer = dr["CInterview_DepartmentOffer"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CInterview_DepartmentOffer"]);
                    retVal.Status = Convert.ToInt32(dr["CInterview_Status"]);
                    retVal.RecruitmentID = Convert.ToInt32(dr["CInterview_RecruitmentID"]);
                    retVal.Represent = dr["CInterview_Represent"].ToString();
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int CInterview_Insert(CInterviewInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_CandidateInterview_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;

            dbCmd.Parameters.AddWithValue("@CInterview_CandidateID", eInfo.CandidateID);
            dbCmd.Parameters.AddWithValue("@CInterview_Ordinal", eInfo.Ordinal);
            if (eInfo.Date != DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@CInterview_Date", eInfo.Date);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_Date", DBNull.Value);
            dbCmd.Parameters.AddWithValue("@CInterview_Interviewer", eInfo.Interviewer);
            if (eInfo.InterviewerPosition == 0)
                dbCmd.Parameters.AddWithValue("@CInterview_InterviewerPosition", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_InterviewerPosition", eInfo.InterviewerPosition);
            dbCmd.Parameters.AddWithValue("@CInterview_OverallComments", eInfo.OverallComments);
            if (eInfo.PositionOffer != 0)
                dbCmd.Parameters.AddWithValue("@CInterview_PositionOffer", eInfo.PositionOffer);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_PositionOffer", DBNull.Value);
            if (eInfo.DepartmentOffer != 0)
                dbCmd.Parameters.AddWithValue("@CInterview_DepartmentOffer", eInfo.DepartmentOffer);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_DepartmentOffer", DBNull.Value);
            dbCmd.Parameters.AddWithValue("@CInterview_Status", eInfo.Status);
            dbCmd.Parameters.AddWithValue("@CInterview_RecruitmentID", eInfo.RecruitmentID);
            dbCmd.Parameters.AddWithValue("@CInterview_Represent", eInfo.Represent);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool CInterview_Update(CInterviewInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_CandidateInterview_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CInterview_ID", eInfo.ID);
            dbCmd.Parameters.AddWithValue("@CInterview_CandidateID", eInfo.CandidateID);
            dbCmd.Parameters.AddWithValue("@CInterview_Ordinal", eInfo.Ordinal);
            if (eInfo.Date != DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@CInterview_Date", eInfo.Date);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_Date", DBNull.Value);

            dbCmd.Parameters.AddWithValue("@CInterview_Interviewer", eInfo.Interviewer);
            
            if (eInfo.InterviewerPosition == 0)
                dbCmd.Parameters.AddWithValue("@CInterview_InterviewerPosition", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_InterviewerPosition", eInfo.InterviewerPosition);
            
            dbCmd.Parameters.AddWithValue("@CInterview_OverallComments", eInfo.OverallComments);
            
            if (eInfo.PositionOffer != 0)
                dbCmd.Parameters.AddWithValue("@CInterview_PositionOffer", eInfo.PositionOffer);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_PositionOffer", DBNull.Value);
            
            if (eInfo.DepartmentOffer != 0)
                dbCmd.Parameters.AddWithValue("@CInterview_DepartmentOffer", eInfo.DepartmentOffer);
            else
                dbCmd.Parameters.AddWithValue("@CInterview_DepartmentOffer", DBNull.Value);
            
            dbCmd.Parameters.AddWithValue("@CInterview_Status", eInfo.Status);
            dbCmd.Parameters.AddWithValue("@CInterview_RecruitmentID", eInfo.RecruitmentID);
            dbCmd.Parameters.AddWithValue("@CInterview_Represent", eInfo.Represent);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool CInterview_Delete(int CInterviewID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_CandidateInterview_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CInterview_ID", CInterviewID);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

    }
}
